
<!DOCTYPE html>
<html lang="zh-cn">
<head>
<meta charset="utf-8"/>
<meta name="viewport" content="width=device-width, initial-scale=1" />
<title>hibernate的hql查询语句总结 - xiaoluo501395377 - 博客园</title>
<link type="text/css" rel="stylesheet" href="/bundles/blog-common.css?v=H3IOkgdPffpmgIkls4TofKh6PlSWVifjfgEhZr2VHqo1"/>
<link id="MainCss" type="text/css" rel="stylesheet" href="/skins/darkgreentrip/bundle-darkgreentrip.css?v=xPXJVC4GCITs1yYYgSeLkcicCxapqseaPVQLFGMO1wc1"/>
<link id="mobile-style" media="only screen and (max-width: 767px)" type="text/css" rel="stylesheet" href="/skins/darkgreentrip/bundle-darkgreentrip-mobile.css?v=6NcJHqsIyaE4w19VtgFvCFahrnr2rYCTRRTdxlMDhhQ1"/>
<link title="RSS" type="application/rss+xml" rel="alternate" href="http://www.cnblogs.com/xiaoluo501395377/rss"/>
<link title="RSD" type="application/rsd+xml" rel="EditURI" href="http://www.cnblogs.com/xiaoluo501395377/rsd.xml"/>
<link type="application/wlwmanifest+xml" rel="wlwmanifest" href="http://www.cnblogs.com/xiaoluo501395377/wlwmanifest.xml"/>
<script src="//common.cnblogs.com/script/jquery.js" type="text/javascript"></script>  
<script type="text/javascript">var currentBlogApp = 'xiaoluo501395377', cb_enable_mathjax=false;var isLogined=false;</script>
<script src="/bundles/blog-common.js?v=Gjc-c4WOecxW2V4mwH9-EN2lPzwMWd4wyAzo535GvA01" type="text/javascript"></script>
<style type="text/css">
[id^="ad_"] {display:none!important;display:none}</style>
</head>
<body>
<a name="top"></a>
<div id="page_begin_html"></div><script>load_page_begin_html();</script>

<!--done-->
<div id="home">
<div id="header">
	<div id="blogTitle">
	<a id="lnkBlogLogo" href="http://www.cnblogs.com/xiaoluo501395377/"><img id="blogLogo" src="/Skins/custom/images/logo.gif" alt="返回主页" /></a>			
		
<!--done-->
<h1><a id="Header1_HeaderTitle" class="headermaintitle" href="http://www.cnblogs.com/xiaoluo501395377/">xiaoluo501395377</a></h1>
<h2></h2>



		
	</div><!--end: blogTitle 博客的标题和副标题 -->
	<div id="navigator">
		
<ul id="navList">
<li><a id="blog_nav_sitehome" class="menu" href="http://www.cnblogs.com/">博客园</a></li>
<li><a id="blog_nav_myhome" class="menu" href="http://www.cnblogs.com/xiaoluo501395377/">首页</a></li>
<li><a id="blog_nav_newpost" class="menu" rel="nofollow" href="https://i.cnblogs.com/EditPosts.aspx?opt=1">新随笔</a></li>
<li><a id="blog_nav_contact" class="menu" rel="nofollow" href="https://msg.cnblogs.com/send/xiaoluo501395377">联系</a></li>
<li><a id="blog_nav_rss" class="menu" href="http://www.cnblogs.com/xiaoluo501395377/rss">订阅</a>
<!--<a id="blog_nav_rss_image" class="aHeaderXML" href="http://www.cnblogs.com/xiaoluo501395377/rss"><img src="//www.cnblogs.com/images/xml.gif" alt="订阅" /></a>--></li>
<li><a id="blog_nav_admin" class="menu" rel="nofollow" href="https://i.cnblogs.com/">管理</a></li>
</ul>
		<div class="blogStats">
			
			<div id="blog_stats">
<span id="stats_post_count">随笔 - 97&nbsp; </span>
<span id="stats_article_count">文章 - 0&nbsp; </span>
<span id="stats-comment_count">评论 - 488</span>
</div>
			
		</div><!--end: blogStats -->
	</div><!--end: navigator 博客导航栏 -->
</div><!--end: header 头部 -->

<div id="main">
	<div id="mainContent">
	<div class="forFlow">
		
<div id="post_detail">
<!--done-->
<div id="topics">
	<div class = "post">
		<h1 class = "postTitle">
			<a id="cb_post_title_url" class="postTitle2" href="http://www.cnblogs.com/xiaoluo501395377/p/3376256.html">hibernate的hql查询语句总结</a>
		</h1>
		<div class="clear"></div>
		<div class="postBody">
			<div id="cnblogs_post_body" class="blogpost-body"><p>这篇随笔将会记录hql的常用的查询语句，为日后查看提供便利。</p>
<p>在这里通过定义了三个类，Special、Classroom、Student来做测试，Special与Classroom是一对多，Classroom与Student是一对多的关系，这里仅仅贴出这三个bean的属性代码：</p>
<p>Special类：</p>
<div class="cnblogs_code">
<pre><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">class</span><span style="color: #000000;"> Special
{
    </span><span style="color: #0000ff;">private</span> <span style="color: #0000ff;">int</span><span style="color: #000000;"> id;
    </span><span style="color: #0000ff;">private</span><span style="color: #000000;"> String name;
    </span><span style="color: #0000ff;">private</span><span style="color: #000000;"> String type;
    </span><span style="color: #0000ff;">private</span> Set&lt;Classroom&gt;<span style="color: #000000;"> rooms;
    ..........
}</span></pre>
</div>
<p>Classroom类：</p>
<div class="cnblogs_code">
<pre><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">class</span><span style="color: #000000;"> Classroom
{
    </span><span style="color: #0000ff;">private</span> <span style="color: #0000ff;">int</span><span style="color: #000000;"> id;
    </span><span style="color: #0000ff;">private</span><span style="color: #000000;"> String name;
    </span><span style="color: #0000ff;">private</span><span style="color: #000000;"> Special special;
    </span><span style="color: #0000ff;">private</span> Set&lt;Student&gt;<span style="color: #000000;"> students;
　  ............
}</span></pre>
</div>
<p>Student类：</p>
<div class="cnblogs_code">
<pre><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">class</span><span style="color: #000000;"> Student
{
    </span><span style="color: #0000ff;">private</span> <span style="color: #0000ff;">int</span><span style="color: #000000;"> id;
    </span><span style="color: #0000ff;">private</span><span style="color: #000000;"> String name;
    </span><span style="color: #0000ff;">private</span><span style="color: #000000;"> String sex;
    </span><span style="color: #0000ff;">private</span><span style="color: #000000;"> Classroom room;
    ..........
}</span></pre>
</div>
<p>1.最简单的查询</p>
<div class="cnblogs_code">
<pre>List&lt;Special&gt; specials = (List&lt;Special&gt;)session.createQuery("select spe from Special spe").list();</pre>
</div>
<p>这是hql最基本的查询语句了，作用就是查出所有的Special对象放到一个List当中</p>
<p>2.基于 ? 的参数化形式</p>
<div class="cnblogs_code">
<pre><span style="color: #008000;">　　　　　　　/**</span><span style="color: #008000;">
             * 查询中使用?，通过setParameter的方式可以防止sql注入
             * jdbc的setParameter的下标从1开始，hql的下标从0开始
             </span><span style="color: #008000;">*/</span><span style="color: #000000;">
            List</span>&lt;Student&gt; students = (List&lt;Student&gt;)session.createQuery("select stu from Student stu where name like ?"<span style="color: #000000;">)
                                                .setParameter(</span>0, "%刘%"<span style="color: #000000;">)
                                                .list();</span></pre>
</div>
<p>&nbsp;</p>
<p>在hql中同样支持基于 ? 的参数化形式查询，<strong>注意</strong>：<span style="color: #0000ff;">在jdbc中，setParameter的下标是从1开始的，而hibernate的setParameter的下标是从0开始的</span>。</p>
<p>3.基于 :xx 的别名的方式设置参数</p>
<div class="cnblogs_code">
<pre><span style="color: #008000;">　　　　　　　/**</span><span style="color: #008000;">
             * 在hql中可以使用别名的方式来查询，格式是 :xxx 通过setParameter来设置别名
             </span><span style="color: #008000;">*/</span><span style="color: #000000;">
            List</span>&lt;Student&gt; students = (List&lt;Student&gt;)session.createQuery("select stu from Student stu where name like :name and sex like :sex"<span style="color: #000000;">)
                                                .setParameter(</span>"name", "%王%").setParameter("sex", "%男%"<span style="color: #000000;">)
                                                .list();</span></pre>
</div>
<p><span>4.如果返回的值只有一个，可以使用uniqueResult方法</span></p>
<div class="cnblogs_code">
<pre><span style="color: #008000;">　　　　　　　/**</span><span style="color: #008000;">
             * 如果得到的值只有一个，则可以使用uniqueResult方法
             </span><span style="color: #008000;">*/</span><span style="color: #000000;">
            Long stu </span>= (Long)session.createQuery("select count(*) from Student stu where name like :name and sex like :sex"<span style="color: #000000;">)
                                                .setParameter(</span>"name", "%王%").setParameter("sex", "%男%"<span style="color: #000000;">)
                                                .uniqueResult();

　　　　　　　</span><span style="color: #008000;">/**</span><span style="color: #008000;">
             * 如果得到的值只有一个，则可以使用uniqueResult方法
             </span><span style="color: #008000;">*/</span><span style="color: #000000;">
            Student stu </span>= (Student)session.createQuery("select stu from Student stu where id = ?"<span style="color: #000000;">)
                                                .setParameter(</span>0, 1<span style="color: #000000;">)
                                                .uniqueResult();</span></pre>
</div>
<p>5.基于投影的查询</p>
<div class="cnblogs_code">
<pre><span style="color: #008000;">　　　　　　　/**</span><span style="color: #008000;">
             * 基于投影的查询，如果返回多个值，这些值都是保存在一个object[]数组当中
             </span><span style="color: #008000;">*/</span><span style="color: #000000;">
            List</span>&lt;Object[]&gt; stus = (List&lt;Object[]&gt;)session.createQuery("select stu.name, stu.sex from Student stu where name like <br />　　　　　　　　　　　　　　　　　　　　　　　　　　　　:name and sex like :sex"<span style="color: #000000;">)
                                                .setParameter(</span>"name", "%张%").setParameter("sex", "%男%"<span style="color: #000000;">)
                                                .list();</span></pre>
</div>
<p>6.基于导航对象的查询</p>
<div class="cnblogs_code">
<pre><span style="color: #008000;">　　　　　　　/**</span><span style="color: #008000;">
             * 如果对象中有导航对象，可以直接通过对象导航查询
             </span><span style="color: #008000;">*/</span><span style="color: #000000;">
            List</span>&lt;Student&gt; stus = (List&lt;Student&gt;)session.createQuery("select stu from Student stu where stu.room.name like :room and sex like :sex"<span style="color: #000000;">)
                                                .setParameter(</span>"room", "%计算机应用%").setParameter("sex", "%女%"<span style="color: #000000;">)
                                                .list();</span></pre>
</div>
<p><strong>注意</strong>:<span style="color: #0000ff;">若直接通过导航对象来查询时，其实际是使用cross join(笛卡儿积)来进行连接查询，这样做性能很差，不建议使用</span></p>
<p>7.使用 in 进行列表查询</p>
<div class="cnblogs_code">
<pre><span style="color: #008000;">　　　　　　　/**</span><span style="color: #008000;">
             * 可以使用in设置基于列表的查询，使用in查询时需要使用别名来进行参数设置，
             * 通过setParameterList方法即可设置，在使用别名和?的hql语句查询时，?形式的查询必须放在别名前面
             </span><span style="color: #008000;">*/</span>
<span style="color: #008000;">//</span><span style="color: #008000;">            List&lt;Student&gt; stus = (List&lt;Student&gt;)session.createQuery("select stu from Student stu where sex like ? and stu.room.id in (:room)")
</span><span style="color: #008000;">//</span><span style="color: #008000;">                                                .setParameter(0, "%女%").setParameterList("room", new Integer[]{1, 2})
</span><span style="color: #008000;">//</span><span style="color: #008000;">                                                .list();</span>
          List&lt;Student&gt; stus = (List&lt;Student&gt;)session.createQuery("select stu from Student stu where stu.room.id in (:room) and stu.sex like :sex"<span style="color: #000000;">)
                                                .setParameterList(</span>"room", <span style="color: #0000ff;">new</span> Integer[]{1, 2}).setParameter("sex", "%女%"<span style="color: #000000;">)
                                                .list();</span></pre>
</div>
<p>在使用 in 进行列表查询时，这个时候要通过 setParameterList() 方法来设置我们的参数，<strong>注意</strong>：<span style="color: #0000ff;">如果一个参数通过别名来传入，一个是通过 ? 的方式来传入的话，那么通过别名的hql语句以及参数设置语句要放在 ? 的后面，不然hibernate会报错。如果都是使用 别名 来设置参数，则无先后顺序</span></p>
<p>8.分页查询</p>
<div class="cnblogs_code">
<pre><span style="color: #008000;">　　　　　　　/**</span><span style="color: #008000;">
             * 通过setFirstResult(0).setMaxResults(10)可以设置分页查询，相当于offset和pagesize
             </span><span style="color: #008000;">*/</span><span style="color: #000000;">
            List</span>&lt;Student&gt; stus = (List&lt;Student&gt;)session.createQuery("select stu from Student stu where stu.room.name like :room and sex like :sex"<span style="color: #000000;">)
                                                .setParameter(</span>"room", "%计算机应用%").setParameter("sex", "%女%").setFirstResult(0).setMaxResults(10<span style="color: #000000;">)
                                                .list();</span></pre>
</div>
<p>9.内连接查询</p>
<div class="cnblogs_code">
<pre><span style="color: #008000;">　　　　　　　/**</span><span style="color: #008000;">
             *    使用对象的导航查询可以完成连接查询，但是使用的是Cross Join(笛卡儿积)，效率不高，所以建议使用join来查询
             </span><span style="color: #008000;">*/</span><span style="color: #000000;">
            List</span>&lt;Student&gt; stus = (List&lt;Student&gt;)session.createQuery("select stu from Student stu join stu.room room where room.id=2"<span style="color: #000000;">)
                                                .list();</span></pre>
</div>
<p>在hql中使用连接查询的语句与我们的sql进行连接查询的语句是有区别的：</p>
<div class="cnblogs_code">
<pre><span style="color: #000000;"><span style="color: #0000ff;"><strong>hql</strong></span>:    select stu from Student stu join stu.room room

<span style="color: #0000ff;"><strong>sql</strong></span>:    select t.* from Student t join Classroom c on t.cid=c.id</span></pre>
</div>
<p>10.左外连和右外连查询</p>
<div class="cnblogs_code">
<pre><span style="color: #008000;">　　　　　　　/**</span><span style="color: #008000;">
             *    左外连和右外连其实是相对的，left join 就是以左边的表为基准， right join 就是以右边的表为基准
             </span><span style="color: #008000;">*/</span><span style="color: #000000;">
            List</span>&lt;Object[]&gt; stus = (List&lt;Object[]&gt;)session.createQuery("select room.name, count(stu.room.id) from Student stu right join stu.room room group by room.id"<span style="color: #000000;">)
                                                .list();</span></pre>
</div>
<p>11.创建DTO类，将查询出来的多个字段可以存放到DTO对象中去</p>
<div class="cnblogs_code">
<pre><span style="color: #008000;">　　　　　　　/**</span><span style="color: #008000;">
             *    <strong><span style="color: #0000ff;">当如果我们查询出多个字段的话，通常会创建一个DTO对象，用来存储我们查询出来的数据，通过 new XXX() 这样的方式</span></strong>
             *    <strong><span style="color: #0000ff;">前提是XXX这个类里面必须要有接受这些字段的构造方法才行，而且必须要使用类的全名
             </span></strong></span><span style="color: #008000;">*/</span>
<span style="color: #008000;">//</span><span style="color: #008000;">            List&lt;Object[]&gt; stus = (List&lt;Object[]&gt;)session.createQuery("select stu.id,stu.name,stu.sex,room.name,special.name from Student stu left join stu.room room left join room.special special")
</span><span style="color: #008000;">//</span><span style="color: #008000;">                                                .list();
</span><span style="color: #008000;">//</span><span style="color: #008000;">            for(Object[] obj : stus)
</span><span style="color: #008000;">//</span><span style="color: #008000;">            {
</span><span style="color: #008000;">//</span><span style="color: #008000;">                System.out.println(obj[0] + ", " + obj[1] + ", " + obj[2] + ", " + obj[3] + ", " + obj[4]);
</span><span style="color: #008000;">//</span><span style="color: #008000;">            }<br /><br /></span>
            List&lt;StudentDTO&gt; stus = (List&lt;StudentDTO&gt;)session.createQuery("select new com.xiaoluo.bean.StudentDTO(stu.id, stu.name, stu.sex, room.name, special.name) from Student stu left join stu.room room left join room.special special")<br />　　　　　　　　　　　　　　　　　　　　　　　　　　　　　.list();</pre>
</div>
<p>12.group having字句</p>
<div class="cnblogs_code">
<pre><span style="color: #008000;">/**</span><span style="color: #008000;">
             * 在hql中不能通过给查询出来的字段设置别名，别名只能设置在from 后面
             </span><span style="color: #008000;">*/</span><span style="color: #000000;">
            List</span>&lt;Object[]&gt; stus = (List&lt;Object[]&gt;)session.createQuery("select special.name, count(stu.room.special.id) from Student stu right join stu.room.special special group by special.id having count(stu.room.special.id)&gt;150"<span style="color: #000000;">)
                                                .list();　　// 查询出人数大于150个人的专业</span></pre>
</div>
<div class="cnblogs_code">
<pre>　　　　　　　<br />　　　　　　　//　　查询出每个专业中男生与女生的个数<br />　　　　　　　List&lt;Object[]&gt; stus = (List&lt;Object[]&gt;)session.createQuery("select special.name, stu.sex, count(stu.room.special.id) from Student stu right join stu.room.special special group by special.id,stu.sex"<span style="color: #000000;">)
                                                .list();</span></pre>
</div>
<p>&nbsp;</p>
<p>基本上用到的hql查询语句就是这些，以后若再遇到会进行补充。</p>
<p>&nbsp;</p></div><div id="MySignature"></div>
<div class="clear"></div>
<div id="blog_post_info_block">
<div id="BlogPostCategory"></div>
<div id="EntryTag"></div>
<div id="blog_post_info">
</div>
<div class="clear"></div>
<div id="post_next_prev"></div>
</div>


		</div>
		<div class = "postDesc">posted @ <span id="post-date">2013-10-18 16:09</span> <a href='http://www.cnblogs.com/xiaoluo501395377/'>xiaoluo501395377</a> 阅读(<span id="post_view_count">...</span>) 评论(<span id="post_comment_count">...</span>)  <a href ="https://i.cnblogs.com/EditPosts.aspx?postid=3376256" rel="nofollow">编辑</a> <a href="#" onclick="AddToWz(3376256);return false;">收藏</a></div>
	</div>
	<script type="text/javascript">var allowComments=true,cb_blogId=130528,cb_entryId=3376256,cb_blogApp=currentBlogApp,cb_blogUserGuid='4892b22a-9cd9-e111-aa3f-842b2b196315',cb_entryCreatedDate='2013/10/18 16:09:00';loadViewCount(cb_entryId);var cb_postType=1;</script>
	
</div><!--end: topics 文章、评论容器-->
</div><a name="!comments"></a><div id="blog-comments-placeholder"></div><script type="text/javascript">var commentManager = new blogCommentManager();commentManager.renderComments(0);</script>
<div id='comment_form' class='commentform'>
<a name='commentform'></a>
<div id='divCommentShow'></div>
<div id='comment_nav'><span id='span_refresh_tips'></span><a href='javascript:void(0);' onclick='return RefreshCommentList();' id='lnk_RefreshComments' runat='server' clientidmode='Static'>刷新评论</a><a href='#' onclick='return RefreshPage();'>刷新页面</a><a href='#top'>返回顶部</a></div>
<div id='comment_form_container'></div>
<div class='ad_text_commentbox' id='ad_text_under_commentbox'></div>
<div id='ad_t2'></div>
<div id='opt_under_post'></div>
<div id='cnblogs_c1' class='c_ad_block'></div>
<div id='under_post_news'></div>
<div id='cnblogs_c2' class='c_ad_block'></div>
<div id='under_post_kb'></div>
<div id='HistoryToday' class='c_ad_block'></div>
<script type='text/javascript'>
    fixPostBody();
    setTimeout(function () { incrementViewCount(cb_entryId); }, 50);
    deliverAdT2();
    deliverAdC1();
    deliverAdC2();    
    loadNewsAndKb();
    loadBlogSignature();
    LoadPostInfoBlock(cb_blogId, cb_entryId, cb_blogApp, cb_blogUserGuid);
    GetPrevNextPost(cb_entryId, cb_blogId, cb_entryCreatedDate, cb_postType);
    loadOptUnderPost();
    GetHistoryToday(cb_blogId, cb_blogApp, cb_entryCreatedDate);   
</script>
</div>


	</div><!--end: forFlow -->
	</div><!--end: mainContent 主体内容容器-->

	<div id="sideBar">
		<div id="sideBarMain">
			
<!--done-->
<div class="newsItem">
<h3 class="catListTitle">公告</h3>
	<div id="blog-news"></div><script type="text/javascript">loadBlogNews();</script>
</div>

			<div id="blog-calendar" style="display:none"></div><script type="text/javascript">loadBlogDefaultCalendar();</script>
			
			<div id="leftcontentcontainer">
				<div id="blog-sidecolumn"></div><script type="text/javascript">loadBlogSideColumn();</script>
			</div>
			
		</div><!--end: sideBarMain -->
	</div><!--end: sideBar 侧边栏容器 -->
	<div class="clear"></div>
	</div><!--end: main -->
	<div class="clear"></div>
	<div id="footer">
		
<!--done-->
Copyright &copy;2017 xiaoluo501395377
	</div><!--end: footer -->
</div><!--end: home 自定义的最大容器 -->
</body>
</html>
